package Util;

import Model.CellMsgModel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by Administrator on 2017/2/9.
 */


public class ExcelUtil {


    public static List<String> getSheetList(String filepath) throws IOException {
        List<String> sheetList = new ArrayList<>();
        String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
        InputStream is = null;
        Workbook wb = null;

        try {
            is = new FileInputStream(filepath);
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                throw new Exception("读取的不是excel文件");
            }
            int sheetSize = wb.getNumberOfSheets();
            for (int i = 0; i < sheetSize; i++) {
                Sheet sheet = wb.getSheetAt(i);
                sheetList.add(sheet.getSheetName());
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return sheetList;
    }


    /**
     * @param filepath
     * @param tamplateSheetName
     * @return 关键字的map,未填值
     * @throws IOException
     */
    public static Map<String, CellMsgModel> getmyExcelKeyList(String filepath,String tamplateSheetName ) throws Exception, FileNotFoundException {
        Map<String, CellMsgModel> parameterMap = new HashMap<>();
        String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
        InputStream is = null;
        Workbook wb = null;
        StringBuilder msg = new StringBuilder();
        try{
            is = new FileInputStream(filepath);
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            }
            else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            }
            else {
                throw new Exception("读取的不是excel文件");
            }
            int sheetSize = wb.getNumberOfSheets();
            for (int i = 0; i < sheetSize; i++){
                Sheet sheet = wb.getSheetAt(i);
                if (sheet.getSheetName().equals(tamplateSheetName)){
                    System.out.println("找到了sheet:" + tamplateSheetName);
                    int rowSize = sheet.getLastRowNum() + 1;
                    for (int j = 0; j < rowSize; j++) {//遍历行
                        Row row = sheet.getRow(j);
                        if (row == null) {//略过空行
                            continue;
                        }
                        int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
                        List<String> rowList = new ArrayList<String>();//对应一个数据行
                        for (int k = 0; k < cellSize; k++) {
                            Cell cell = row.getCell(k);
                            if (cell != null) {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                String value = cell.getStringCellValue();
                                if(value.startsWith("${") && !value.endsWith("}")){
                                        //如果有这个情况应该提示是否是有问题
                                    msg.append("疑似key设置不完整:" + value + " row:" + j +" col:"+k + "\r\n");
                                }
                                if(value.startsWith("${") && value.endsWith("}")){
                                    //System.out.println(value);
                                    CellMsgModel cellMsgModel = new CellMsgModel();
                                    cellMsgModel.setRow(j);
                                    cellMsgModel.setCol(k);
                                    cellMsgModel.setValue("");

                                    if(parameterMap.containsKey(value)){
                                        //如果重复了的话应该加入到一个重复的表里面提供检查
                                        msg.append("key值重复:" + value + " row:" + j +" col:"+k + "---- row:" + parameterMap.get(value).getRow() + " col:" + parameterMap.get(value).getCol() + "\r\n");
                                        //System.out.println(value +":" + k + "," + j + "----" + parameterMap.get(value).getCol()+ "," + parameterMap.get(value).getRow());
                                    }


                                    parameterMap.put(value,cellMsgModel);
                                }
                            }

                        }
                    }
                }
            }
        } catch (Exception e) {
            throw e;
        } finally {
            if (is != null) {
                is.close();
            }
        }
        System.out.println("收到的msg的长度" + msg.length());
        if(msg.length() != 0){
            System.out.println(msg.toString());
            Exception e = new Exception(msg.toString());
            throw e;
        }
        return parameterMap;
    }

    public static Map<String, CellMsgModel> getmyExcelMsg(String filepath,String tamplateSheetName ,Map<String, CellMsgModel> parameterMap) throws IOException {
        String fileType = filepath.substring(filepath.lastIndexOf(".") + 1, filepath.length());
        InputStream is = null;
        Workbook wb = null;

        try{
            is = new FileInputStream(filepath);
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            }
            else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            }
            else {
                throw new Exception("读取的不是excel文件");
            }
            int sheetSize = wb.getNumberOfSheets();
            for (int i = 0; i < sheetSize; i++){
                Sheet sheet = wb.getSheetAt(i);
                if (sheet.getSheetName().equals(tamplateSheetName)){
                    System.out.println("找到了sheet:" + tamplateSheetName);
                    for ( String s :
                            parameterMap.keySet()) {
                        int row = parameterMap.get(s).getRow();
                        int col = parameterMap.get(s).getCol();
                        Cell cell = sheet.getRow(row).getCell(col);
                        if (cell != null) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String value = cell.getStringCellValue();
                            if (isNumeric(value)){
                                BigDecimal b = new BigDecimal(value);
                                //,代表分隔符
                                //0.后面的##代表位数 如果换成0 效果就是位数不足0补齐
                                DecimalFormat d1 =new DecimalFormat("#,##0.00;(#)");
                                value = d1.format(b);
                            }
                            parameterMap.get(s).setValue(value);
                        }
                        else{
                            parameterMap.get(s).setValue("");
                        }
                    }
                }
                else{
                    continue;
                }
            }
        }catch (FileNotFoundException e){
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                is.close();
            }
        }
        return parameterMap;
    }

    public static boolean isNumeric(String str){
        Pattern pattern = Pattern.compile("^\\-?[0-9]+\\.?[0-9]*$");
        Matcher isNum = pattern.matcher(str);
        if( !isNum.matches() ){
            return false;
        }
        return true;
    }
}
